"""
Case Type   : 兼容性-alter table语句删除主键
Case Name   : 开启2个会话，事务中执行删除主键操作
Create At   : 2022.10.27
Owner       : z@wan005
Description :
    1、创建兼容b类型数据库
    2、创建表
    3、开启事务，删除主键
    4、线程2再次插入主键列重复值
    5、清理环境
Expect      :
    1、成功
    2、创建成功
    3、成功
    4、会话1事务未提交插入数据阻塞，待会话1提交事务后，会话2插入数据成功
    5、清理环境成功
History     :
"""

import os
import time
import unittest

from testcase.utils.ComThread import ComThread
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class Compatibility(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = 'db_alter_0044'
        self.t_name = 't_alter_0044'

    def test_alter_table(self):
        step1_txt = '----step1: 创建兼容b类型数据库，expect: 成功----'
        self.log.info(step1_txt)
        sql_cmd1 = f"set dolphin.b_compatibility_mode to off;" \
                   f"drop database if exists {self.db_name};" \
                   f"create database {self.db_name} dbcompatibility 'b';"
        msg1 = self.pri_sh.execut_db_sql(sql_cmd1)
        self.log.info(msg1)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, msg1,
                      '执行失败:' + step1_txt)

        step2_txt = '----step2: 创建表，expect: 创建成功----'
        self.log.info(step2_txt)
        sql2 = f"drop table if exists {self.t_name};" \
               f"create  table {self.t_name}(id int primary key, sex text);" \
               f"insert into {self.t_name} values(1,'man');"
        msg2 = self.pri_sh.execut_db_sql(sql2, dbname=self.db_name)
        self.log.info(msg2)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, msg2,
                      '执行失败:' + step2_txt)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, msg2,
                      '执行失败:' + step2_txt)

        step3_txt = '----step3: 开启事务，删除主键;expect:成功----'
        self.log.info(step3_txt)
        sql_cmd1 = f'start transaction;' \
                   f'alter table {self.t_name} drop primary key;' \
                   f'select pg_sleep(10);' \
                   f'commit;'
        session1 = ComThread(self.pri_sh.execut_db_sql,
                             args=(sql_cmd1, '', self.db_name))
        session1.setDaemon(True)
        session1.start()
        time.sleep(3)

        step4_txt = '----step4: 线程2再次插入主键列重复值，expect: 会话1事务未提' \
                    '交插入数据阻塞，待会话1提交事务后，会话2插入数据成功----'
        self.log.info(step4_txt)
        sql_cmd2 = f"insert into {self.t_name} values(1,'man');"
        session2 = ComThread(self.pri_sh.execut_db_sql,
                             args=(sql_cmd2, '', self.db_name))
        session2.setDaemon(True)
        session2.start()

        self.log.info("----session1执行结果----")
        session1.join(50)
        session1_result = session1.get_result()
        self.log.info(session1_result)
        self.assertIn('START TRANSACTION', session1_result,
                      '执行失败:' + step3_txt)
        self.assertIn('ALTER TABLE', session1_result,
                      '执行失败:' + step3_txt)
        self.assertIn('COMMIT', session1_result,
                      '执行失败:' + step3_txt)

        self.log.info("----session2执行结果----")
        session2.join(60)
        session2_result = session2.get_result()
        self.log.info(session2_result)
        self.assertIn('INSERT 0 1', session2_result,
                      '执行失败:' + step4_txt)

    def tearDown(self):
        step5_txt = '----step5: 删除数据库----'
        self.log.info(step5_txt)
        drop_sql = f'reset dolphin.b_compatibility_mode; drop database if exists {self.db_name};'
        drop_result = self.pri_sh.execut_db_sql(drop_sql)
        self.log.info(drop_result)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_result,
                      "执行失败" + step5_txt)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
